package org.traffometer.traffobase.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import javax.servlet.ServletContext;

import org.traffometer.common.data.TraffoPacket;
import org.traffometer.opendmtp.protocol.GeoPoint;

/**
 * Provide persistency for the traffobase project using Derby database.
 * 
 * @author Kiet Huynh
 */
public class DerbyPacketStore implements PacketStore {

  /**
   * The data type double used by the database.
   */
  private static final String doubleType = " DOUBLE, ";
  /**
   * The key to be used to retrieve the connection to the database.
   */
  public static final String DBCONNECTION_KEY = "org.traffometer.traffobase.connection";

  /**
   * The name of the table to store the contents of the PacketStore.
   */
  public static final String table = "packetStore";
  
  /**
   * The first column of the table to store the primary key.
   */
  public static final String column1 = "id";
  
  /**
   * The second column of the table to store the latitude 1 of the TraffoPacket.
   */
  public static final String column2 = "latitude1";
  
  /**
   * The third column of the table to store the latitude 2 of the TraffoPacket.
   */
  public static final String column3 = "latitude2";
  
  /**
   * The forth column of the table to store the longitude 1 of the TraffoPacket.
   */
  public static final String column4 = "longitude1";
  
  /**
   * The fifth column of the table to store the longitude 2 of the TraffoPacket.
   */
  public static final String column5 = "longitude2";
  
  /**
   * The sixth column of the table to store the timestamp 1 of the TraffoPacket.
   */
  public static final String column6 = "timestamp1";
  
  /**
   * The seventh column of the table to store the timestamp 2 of the TraffoPacket.
   */
  public static final String column7 = "timestamp2";
  
  /**
   * The eighth column of the table to store the vehicle type of the TraffoPacket.
   */
  public static final String column8 = "vehicleType";
  
  /**
   * A List of TraffoPackets.
   */
  private final List<TraffoPacket> store;


  /**
   * The ServletContext.
   */
  public ServletContext context;
  
  /**
   * The ServletContext.
   */
  public Integer rowCount;
  private final Connection connection;
  private PreparedStatement selectSQL;
  private PreparedStatement countRowSQL;
  private PreparedStatement insertRowSQL;
  private PreparedStatement deleteRowSQL;

  /**
   * Creates a new DerbyPacketStore instance, restoring its state from the database if it exists.
   * 
   * @param connectionKey The Key to connect to the database.
   * @param dbConnection The connection to the database.
   */
  public DerbyPacketStore(String connectionKey, Connection dbConnection) {
    //this.context = context;
    //connection = (Connection) this.context.getAttribute(DBCONNECTION_KEY);
    connection = dbConnection;
    //DBCONNECTION_KEY = connectionKey;
    this.store = new ArrayList<TraffoPacket>();
    this.init();
  }

  /**
   * Set up the program. Creates the PacketStore table to store the contents of the actual
   * PacketStore and SQL statements. If the table already exists, the contents of the stack is
   * restored by using the contents of the table.
   * 
   */
  private void init() {
    try {
      if (createTable()) {
        rowCount = 0;
        prepareSQL();
      }
      else {
        prepareSQL();
        restorePacketStore();
        countRow();
      }
    }
    catch (Exception e) {
      e.printStackTrace();
      //this.context.log("StackMVCdb exception during setUp.", e);
    }
  }
  
  /**
   * Prepares the SQL statements to access to the DerbyPacketStore table.
   * 
   */
  private void prepareSQL() {
    String countRowStatement = "SELECT COUNT(*) FROM " + table;
    String selectStatement = "SELECT " + column1 + ", " + column2 + ", " + column3 + 
      ", " + column4 + ", " + column5 + ", " +  column6 + ", " + column6 + ", " + 
      column7 + ", " + column8 + " FROM " + table;
    String insertRowStatement = "INSERT INTO " + table + 
      " VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
    String deleteRowStatement = "DELETE FROM " + table + " WHERE " + column1
    + " = ?";
    try {
      countRowSQL = connection.prepareStatement(countRowStatement);
      selectSQL = connection.prepareStatement(selectStatement);
      insertRowSQL = connection.prepareStatement(insertRowStatement);
      deleteRowSQL = connection.prepareStatement(deleteRowStatement);
    }
    catch (Exception e) {
      e.printStackTrace();
      this.context.log("StackMVCdb exception during prepareSQL.", e);
    }
  }

  /**
   * Counts the number of rows of the stack table.
   * @throws SQLException If there are problem with the database
   * 
   */
  private void countRow() throws SQLException {
    ResultSet result = null;
    try {
      result = countRowSQL.executeQuery();
      result.next();
      rowCount = result.getInt(1);
      //result.close();
    }
    catch (Exception e) {
      e.printStackTrace();
      this.context.log("DerbyPacketStore exception during countRow.", e);
    }
    finally {
      result.close();
    }
  }

  /**
   * Creates the DerbyPacketStore table.
   * 
   * @return true if the table creation succeeds.
   */
  private boolean createTable() {
    String createTableSQL = "CREATE TABLE " + table + "(" + column1 + " INT PRIMARY KEY, "
        + column2 + doubleType + column3 + doubleType + column4 + doubleType + column5 + 
        doubleType + column6 + " BIGINT, " + column7 + " BIGINT, " + column8 + " VARCHAR(15))";
    boolean result = true;
    Statement statement = null;
    try {
      statement = connection.createStatement();
      statement.executeUpdate(createTableSQL);
      rowCount = 0;
    }
    catch (SQLException e) {
      result = false;
      //this.context.log(
         // "DerbyPacketStore this is supposed not to be an error. The table already exists.", e);
    }
    catch (Exception e) {
      e.printStackTrace();
      //this.context.log("DerbyPacketStore exception during createTable.", e);
    }
    finally {
      try {
        statement.close();
      }
      catch (SQLException e) {
        e.printStackTrace();
      }
    }
    return result;
  }

  /**
   * Drop the stack table.
   * 
   */
  private void dropTable() {
    String dropTableSQL = "DROP TABLE " + table;
    Statement statement = null;
    try {
      statement = connection.createStatement();
      statement.execute(dropTableSQL);
    }
    catch (Exception e) {
      e.printStackTrace();
      this.context.log("DerbyPacketStore exception during dropTable.", e);
    }
    finally {
      try {
        statement.close();
      }
      catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }
    }
  }

  /**
   * Insert the new row into the stack table.
   * 
   * @param lat1 The latitude 1 of the TraffoPacket.
   * @param lat2 The latitude 2 of the TraffoPacket.
   * @param long1 The longitude 1 of the TraffoPacket.
   * @param long2 The longitude 2 of the TraffoPacket.
   * @param time1 The timestamp 1 of the TraffoPacket.
   * @param time2 The timestamp 2 of the TraffoPacket.
   * @param vehicle The vehicle type of the TraffoPacket.
   * 
   */
  private void insertRow(double lat1, double lat2, double long1,
      double long2, long time1, long time2, String vehicle) {
    try {
      insertRowSQL.setInt(1, rowCount++);
      insertRowSQL.setDouble(2, lat1);
      insertRowSQL.setDouble(3, lat2);
      insertRowSQL.setDouble(4, long1);
      insertRowSQL.setDouble(5, long2);
      insertRowSQL.setLong(6, time1);
      insertRowSQL.setLong(7, time2);
      insertRowSQL.setString(8, vehicle);
      insertRowSQL.executeUpdate();
    }
    catch (Exception e) {
      e.printStackTrace();
      this.context.log(" exception during insertRow.", e);
    }
  }

  /**
   * Delete the row that was stored last time.
   */
   
  private void deleteRow() {
    try {
      rowCount--;
      deleteRowSQL.setInt(1, rowCount);
      deleteRowSQL.executeUpdate();
    }
    catch (Exception e) {
      e.printStackTrace();
      this.context.log("DerbyPacketStore exception during deleteRow.", e);
    }
  } 

  /**
   * Restores the DerbyPacketStore instance from disk.
   */
  private void restorePacketStore() {
    this.store.clear();
    double lat1, long1, lat2, long2;
    long time1, time2;
    String vehicleType;
    ResultSet result = null;
    try {
      result = selectSQL.executeQuery();
      while (result.next()) {
        lat1 = (result.getDouble(column2));
        lat2 = (result.getDouble(column3));
        long1 = (result.getDouble(column4));
        long2 = (result.getDouble(column5));
        time1 = (result.getLong(column6));
        time2 = (result.getLong(column7));
        vehicleType = (result.getString(column8));
        this.store.add(
            new TraffoPacket(
                new GeoPoint(lat1, long1, time1), 
                new GeoPoint(lat2, long2, time2),
                vehicleType));
      }
      //result.close();
    }
    catch (Exception e) {
      System.out.println("DerbyPacketStore exception during restorePacketStore. See log. " + e);
      this.context.log("DerbyPacketStore exception during restorePacketStore.", e);
    }
    finally {
      try {
        result.close();
      }
      catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }
    }
  }

  
  /**
   * Adds a packet to this PacketStore. First restores packets from the Derby table, then adds a
   * packet, then saves the packets back to the table. This method does not do any file locking, so
   * conflicts are possible.
   * 
   * @param packet a packet to add
   * @return true if the packet is successfully added.
   * @see org.traffometer.traffobase.model.PacketStore#add(org.traffometer.common.data.TraffoPacket)
   */
  public boolean add(TraffoPacket packet) {
    boolean retVal;
    restorePacketStore();
    retVal = this.store.add(packet);
    double lat1 = packet.getLatitude1();
    double lat2 = packet.getLatitude2();
    double long1 = packet.getLongitude1();
    double long2 = packet.getLongitude2();
    long time1 = packet.getTimeStamp1();
    long time2 = packet.getTimeStamp2();
    String vehicle = packet.getVehicleType();
    this.insertRow(lat1, lat2, long1, long2, time1, time2, vehicle);
    return retVal;
  }
 
  /**
   * Removes the packets from this FilePacketStore, then overwrites the DerbyPacketStore table 
   * with the empty table.
   * 
   * @see org.traffometer.traffobase.model.PacketStore#clear()
   */
  public void clear() {
    this.store.clear();
    dropTable();
    createTable();
  }
  
  /**
   * Getter for list of TraffoPackets.
   * 
   * @return the packets
   */
  public List<TraffoPacket> getPackets() {
    return store;
  }
  
  /**
   * Returns an iterator over this DerbyPacketStore. First restores from the table in case 
   * another process has made changes.
   * 
   * @return an iterator over this DerbyPacketStore
   * @see org.traffometer.traffobase.model.PacketStore#iterator()
   */
  public Iterator<TraffoPacket> iterator() {
    restorePacketStore();
    return this.store.iterator();
  }
}
